<?php
if( !defined( '_VALID_MOS' ) && !defined( '_JEXEC' ) ) die( 'Direct Access to '.basename(__FILE__).' is not allowed.' );  

mm_showMyFileName( __FILE__ );
require_once dirname(__FILE__).'/../../../../libraries/phpExcelReader/Excel/reader.php';

class vm_ps_importdata {
    function processImport() {        
        $vendor_id = (int)vmGet( $_REQUEST, 'vendor_id');
        if ($vendor_id == 0) {
            require_once ( CLASSPATH. 'ps_user.php');
            $ps_user = new ps_user;
            $user =& JFactory::getUser();
            $vendor_id = $ps_user->getUserInfomations($user->get('id'))->record[0]->vendor_id;
        }
        
        $filename = vmGet( $_REQUEST, 'fname');
        $filename = dirname( __FILE__ ).'/../../../../media/excels/'.$filename;
        
        // ExcelFile($filename, $encoding);
        $data = new Spreadsheet_Excel_Reader();
        
        // Set output Encoding.
        $data->setOutputEncoding('CP1251');

        $data->read($filename);

        $arrList = array();
        $category_id = 0;
        $category_child_id = 0;
        for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {
            $cell1 = (int)$data->sheets[0]['cells'][$i][1]; // Category Id Or Product Id
            $cell2 = $data->sheets[0]['cells'][$i][2]; // Category Name Or Product Name
            $cell3 = $data->sheets[0]['cells'][$i][3]; // Category Desc Or Product Desc
            
            if (trim($cell2) != '') {
                $cell4 = $data->sheets[0]['cells'][$i][4]; // Attibute
                $cell5 = $data->sheets[0]['cells'][$i][5]; // Product Price
                $cell6 = $data->sheets[0]['cells'][$i][6]; // Product Special
                
                // If Description is empty and Price is empty and Special is empty => category
                if ($cell3 == '' && $cell5 == '' && $cell6 == '') {
                    $category_id = (int)$cell1;
                    $category_name = $cell2;
                    $category_desc = $cell3;
                    
                    // Update to the category table
                    if ($category_id > 0 || ps_importdata::checkExistDataByCategory($category_id)) {
                        ps_importdata::updateCategory($cell1, $category_name, $category_desc);
                    } else {
                        $category_id = ps_importdata::addCategory($vendor_id, $category_name, $category_desc);
                        
                        if ($category_child_id == 0) {
                            $category_child_id = ps_importdata::getCategoryId($vendor_id);
                        }
                        
                        // Insert to the category_xhref table
                        ps_importdata::addCategoryXref($category_child_id, $category_id);
                    }
                } else {
                    $product_id = (int)$cell1;
                    $product_name = $cell2;
                    $product_desc = $cell3;
                    $attribute = $cell4;
                    $product_price = $cell5;
                    $product_special = $cell6;
                                        
                    // Insert to the product table
                    if ($product_id > 0 || ps_importdata::checkExistDataByProduct($product_id)) {
                        ps_importdata::updateProduct($product_id, $product_name, $product_desc, $product_special, $attribute);
                        ps_importdata::updateProductPrice($product_id, $product_price);
                    } else {                        
                        $product_id = ps_importdata::addProduct($vendor_id, $product_name, $product_desc, $product_special, $attribute);
                        
                        // Insert to the product_category_xref table
                        ps_importdata::addProductCategoryXref($category_id, $product_id);
                        
                        // Insert to the product_mx_xref table
                        ps_importdata::addProductMfXref($product_id);
                        
                        // Insert to the product_price table
                        ps_importdata::addProductPrice($product_id, $product_price);
                    }
                }
            }
        }
        
        // Delete file after imported
        ps_importdata::deleteImageSrc($filename);
    }
    
    function checkExistDataByCategory($category_id) {
        $q = 'SELECT category_id
              FROM #__{vm}_category
              WHERE category_id = '.$category_id;
              
        $db = new ps_DB;      
        $db->query( $q );
        $db->next_record();
        
        $category_id = (int)$db->f("category_id");
        
        if ($category_id > 0) {
            return true;
        }
        
        return false;
    }
    
    function checkExistDataByProduct($product_id) {
        $q = 'SELECT product_id
              FROM #__{vm}_product
              WHERE product_id = '.$product_id;
              
        $db = new ps_DB;      
        $db->query( $q );
        $db->next_record();
        
        $product_id = (int)$db->f("product_id");
        
        if ($product_id > 0) {
            return true;
        }
        
        return false;
    }
    
    function getCategoryId($vendor_id) {
        $q = 'SELECT c_x.category_child_id 
              FROM #__{vm}_category c
                    INNER JOIN #__{vm}_category_xref c_x ON c.category_id = c_x.category_child_id
              WHERE c.vendor_id = '.$vendor_id.' and c_x.category_parent_id!= 0';
        
        $db = new ps_DB;      
        $db->query( $q );
        $db->next_record();

        $category_child_id = intval($db->f("category_child_id"));
        
        return $category_child_id;
    }
    
    function deleteImageSrc($filename) {
        jimport('joomla.filesystem.file');
                
        JFile::delete($filename);
    }
    
    function getListOrder($parent_category_id) {
        $db = new ps_DB;
        
        $q = 'SELECT MAX(list_order) AS list_order 
              FROM #__{vm}_category_xref,#__{vm}_category
              WHERE category_parent_id='.$parent_category_id.'
                AND category_child_id=category_id';
        
        $db->query( $q );
        $db->next_record();

        $list_order = intval($db->f("list_order"))+1;
        
        return $list_order;
    }
    
    function addCategory($vendor_id, $category_name, $category_desc) {
        $timestamp = time();
        
        $list_order = ps_importdata::getListOrder(3);
        
        $fields = array('vendor_id' => $vendor_id,
                        'category_name' => addslashes($category_name),
                        'category_description' => addslashes($category_desc),
                        'category_publish' => 'Y',
                        'cdate' => $timestamp,
                        'mdate' => $timestamp,
                        'category_browsepage' => 'browse_1',
                        'products_per_row' => 1,
                        'category_flypage' => 'flypage.tpl',
                        'list_order' => $list_order);
        
        $db = new ps_DB;
        $db->buildQuery('INSERT', '#__{vm}_category', $fields );
        $db->query();
        
        $category_id = $db->last_insert_id();
        
        return $category_id;
    }
    
    function updateCategory($category_id, $category_name, $category_desc) {
        $timestamp = time();
                
        $fields = array('category_name' => addslashes($category_name),
                        'category_description' => addslashes($category_desc),
                        'mdate' => $timestamp);
        
        $db = new ps_DB;
        $db->buildQuery('UPDATE', '#__{vm}_category', $fields, 'WHERE category_id = '.$category_id );
        $db->query();
    }
    
    function addCategoryXref($parent_category_id, $category_id) {
        $fields = array('category_parent_id' => $parent_category_id,
                        'category_child_id' => $category_id);
        
        $db = new ps_DB;
        $db->buildQuery('INSERT', '#__{vm}_category_xref', $fields );
        $db->query();
    }
    
    function addProduct($vendor_id, $product_name, $product_desc, $product_special, $attribute) {
        $timestamp = time();
        
        $fields = array('vendor_id' => $vendor_id,
                        'product_parent_id' => 0,
                        'product_desc' => addslashes($product_desc),
                        'product_publish' => 'Y',
                        'product_available_date' => $timestamp, 
                        'cdate' => $timestamp,
                        'mdate' => $timestamp,
                        'product_name' => addslashes($product_name),
                        'product_special' => $product_special, 
                        'attribute' => $attribute);
        
        $db = new ps_DB;
        $db->buildQuery('INSERT', '#__{vm}_product', $fields );
        $db->query();
        
        $product_id = $db->last_insert_id();
        
        // Update sku
        $fields = array('product_sku' => $product_id.'');
        $db->buildQuery('UPDATE', '#__{vm}_product', $fields, 'WHERE product_id = '.$product_id );
        $db->query();
        
        return $product_id;
    }
    
    function updateProduct($product_id, $product_name, $product_desc, $product_special, $attribute) {
        $timestamp = time();
        
        $fields = array('product_desc' => addslashes($product_desc),
                        'mdate' => $timestamp,
                        'product_name' => addslashes($product_name),
                        'product_special' => $product_special, 
                        'attribute' => $attribute);
        
        $db = new ps_DB;
        $db->buildQuery('UPDATE', '#__{vm}_product', $fields, 'WHERE product_id = '.$product_id );
        $db->query();
    }
    
    function addProductCategoryXref($category_id, $product_id) {
        $fields = array('category_id' => $category_id,
                        'product_id' => $product_id,
                        'product_list' => 1);
        
        $db = new ps_DB;
        $db->buildQuery('INSERT', '#__{vm}_product_category_xref', $fields );
        $db->query();
    }
    
    function addProductMfXref($product_id) {
        $fields = array('product_id' => $product_id,
                        'manufacturer_id' => 1);
        
        $db = new ps_DB;
        $db->buildQuery('INSERT', '#__{vm}_product_mf_xref', $fields );
        $db->query();
    }
    
    function addProductPrice($product_id, $product_price) {
        $timestamp = time();
        
        $fields = array('product_id' => $product_id,
                        'product_price' => $product_price,
                        'product_currency' => 'USD',
                        'cdate' => $timestamp,
                        'mdate' => $timestamp,
                        'shopper_group_id' => 5);
        
        $db = new ps_DB;
        $db->buildQuery('INSERT', '#__{vm}_product_price', $fields );
        $db->query();
    }
    
    function updateProductPrice($product_id, $product_price) {
        $timestamp = time();
        
        $fields = array('product_id' => $product_id,
                        'product_price' => $product_price,
                        'mdate' => $timestamp);
        
        $db = new ps_DB;
        $db->buildQuery('UPDATE', '#__{vm}_product_price', $fields, 'WHERE product_id = '.$product_id );
        $db->query();
    }
}

// Check if there is an extended class in the Themes and if it is allowed to use them
// If the class is called outside Virtuemart, we have to make sure to load the settings
// Thomas Kahl - Feb. 2009
if (!defined('VM_ALLOW_EXTENDED_CLASSES') && file_exists(dirname(__FILE__).'/../virtuemart.cfg.php')) {
    include_once(dirname(__FILE__).'/../virtuemart.cfg.php');
}
// If settings are loaded, extended Classes are allowed and the class exisits...
if (defined('VM_ALLOW_EXTENDED_CLASSES') && defined('VM_THEMEPATH') && VM_ALLOW_EXTENDED_CLASSES && file_exists(VM_THEMEPATH.'user_class/'.basename(__FILE__))) {
    // Load the theme-user_class as extended
    include_once(VM_THEMEPATH.'user_class/'.basename(__FILE__));
} else {
    // Otherwise we have to use the original classname to extend the core-class
    class ps_importdata extends vm_ps_importdata {}
} 
?>
